package com.dstz.form.manager.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import com.dstz.base.core.cache.ICache;
import com.dstz.sys.util.ContextUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dstz.base.api.response.impl.ResultMsg;
import com.dstz.base.core.util.BeanUtils;
import com.dstz.base.core.util.ExceptionUtil;
import com.dstz.base.core.util.StringUtil;
import com.dstz.form.constants.BooleanConstants;
import com.dstz.form.constants.FieldsConstants;
import com.dstz.form.constants.ViewConstants.BtnClickTypeConstants;
import com.dstz.form.constants.ViewConstants.ViewExcuConstants;
import com.dstz.form.manager.FormCustSqlFieldsManager;
import com.dstz.form.manager.FormCustSqlManager;
import com.dstz.form.manager.FormCustSqlViewManager;
import com.dstz.form.model.custsql.FormCustSql;
import com.dstz.form.model.custsql.FormCustSqlFields;
import com.dstz.form.request.SqlViewRequest;
import com.dstz.form.util.ArrayUtil;
import com.dstz.form.util.DateUtils;
import com.dstz.form.util.JSONObjectUtil;
import com.dstz.form.util.JdbcTemplateUtil;
import com.dstz.form.util.JxlUtils;
import com.dstz.form.util.SqlUtil;
import com.dstz.form.util.Util;
import com.dstz.form.util.ViewUtil;
import com.dstz.form.vo.SqlButtonDataChangeVO;
import com.dstz.form.vo.SqlButtonVO;
import com.dstz.form.vo.SqlConstructVO;
import com.dstz.form.vo.SqlQueryDataVO;
import com.dstz.form.vo.SqlViewDataVO;
import com.dstz.form.vo.SqlViewVO;
import com.dstz.sys.api.freemark.IFreemarkerEngine;
import com.dstz.sys.api.model.dto.DataDictDTO;
import com.dstz.sys.api.service.DataDictService;
import com.fasterxml.jackson.databind.ObjectMapper;

import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * @description:自定义列表View
 * @company:宇宙大魔王
 * @author:xianggang
 * @date:2019-08-31 12:21:58
 */
@Service("formCustSqlViewManager")
public class FormCustSqlViewManagerImpl implements FormCustSqlViewManager {
	
	protected Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Resource
	FormCustSqlManager formCustSqlManager;
	
	@Resource
	FormCustSqlFieldsManager formCustSqlFieldsManager;
	
	@Autowired
	IFreemarkerEngine freemarkerEngine;
	
	@Resource
	DataDictService dataDictService;

	@Autowired
	private ICache<String> icache;
	/**
	 * 根据别名获取自定义sql对象
	 * @param code
	 * @return
	 * @author xianggang
	 * @date 2019年9月2日 下午2:04:47
	 */
	@Override
	public ResultMsg<FormCustSql> getVo(String code) {
		
		if (StringUtil.isEmpty(code)) {
			return ResultMsg.ERROR("ID不能为空");
		}
		
		FormCustSql formCustSql = formCustSqlManager.getByCode(code, true);
		if (formCustSql == null) {
			return ResultMsg.ERROR("自定义列表不存在,code:" + code);
		}
		
		if (BooleanConstants.NO == formCustSql.getStatus()) {
			return ResultMsg.ERROR("自定义列表已被禁用");
		}
		
		// 剔除一些不能展示的字段
		formCustSql.setSqlContent(null);
		formCustSql.setCreateBy(null);
		formCustSql.setCreateTime(null);
		formCustSql.setCreator(null);
		formCustSql.setUpdateBy(null);
		formCustSql.setUpdateTime(null);
		formCustSql.setUpdator(null);
		formCustSql.setTableName(null);
		formCustSql.setDsKey(null);
		
		// 处理按钮
		String buttons = formCustSql.getButtons();
		if (StringUtil.isNotEmpty(buttons)) {
			List<SqlButtonVO> buttonList = JSONObjectUtil.toList(buttons, SqlButtonVO.class);
			for (SqlButtonVO sqlButtonVO : buttonList) {
				// 去掉数值变化,防止前端可以看见
				sqlButtonVO.setDataChange(null);
			}
			formCustSql.setButtons(JSONArray.toJSONString(buttonList));
		}
		
		return ResultMsg.SUCCESS(formCustSql);
	}
	
	/**
	 * 查询自定义sql数据
	 * @param request
	 * @return
	 * @author xianggang
	 * @date 2019年9月2日 下午2:16:36
	 */
	@Override
	public ResultMsg<SqlViewDataVO> listData(SqlViewRequest request) {
		
		String code = request.getCode();
		Integer curPage = request.getCurPage();
		Integer pageSize = request.getPageSize();
		
		ResultMsg<SqlViewVO> listDataSql = listDataSql(request);
		SqlViewVO sqlViewVO = listDataSql.getData();
		
		// 获取计算总条数的sql
		String queryCountSql = sqlViewVO.getQueryCountSql();
		
		// 获取查询分页数据的sql
		String queryDataSql = sqlViewVO.getQueryDataSql();
		
		// 获取查询统计数据的sql
		String querySumSql = sqlViewVO.getQuerySumSql();
		
		String dsKey = sqlViewVO.getDsKey();
		
		JdbcTemplate jdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dsKey);
		
		// 获取数据
		List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(queryDataSql);
		
		String list = ViewUtil.changeQueryListToJSONStr(queryForList);
		
		int count = 0;
		
		// 优化获取总数的逻辑,如果查询出来的数据小于分页大小,那么就没必要再查总数了.直接计算就好
		if (queryForList.size() < pageSize) {
			count = (curPage - 1) * pageSize + queryForList.size();
		} else {
			// 再查一遍,获取总数
			count = jdbcTemplate.queryForObject(queryCountSql, Integer.class);
		}
		
		// 处理数据
		Object listData = ViewUtil.handleData(code, list, jdbcTemplate, sqlViewVO);
		
		SqlViewDataVO sqlViewDataVO = new SqlViewDataVO();
		
		sqlViewDataVO.setCount(count);
		sqlViewDataVO.setList(listData);
		
		// 获取统计数据
		if (StringUtils.isNotEmpty(querySumSql)) {
			Map<String, Object> queryForMap = jdbcTemplate.queryForMap(querySumSql);
			sqlViewDataVO.setSumData(queryForMap);
		}
		
		return ResultMsg.SUCCESS(sqlViewDataVO);
	}
	
	/**
	 * 查询自定义sql的执行语句对象
	 * @param request
	 * @return
	 * @return ResultMsg<FormCustSql>
	 * @author xianggang
	 * @date 2019年9月2日 下午2:16:20
	 */
	@Override
	public ResultMsg<SqlViewVO> listDataSql(SqlViewRequest request) {
		
		String code = request.getCode();
		String queryData = request.getParams();
		Integer pageNo = request.getCurPage();
		Integer pageSize = request.getPageSize();
		String projectid=icache.getByKey("projectid");

		icache.add("projectid"+StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentUserId()),code);

		JSONObject queryDataObject = JSONObject.parseObject(queryData);
		SqlQueryDataVO sqlQueryDataVO = JSONObject.toJavaObject(queryDataObject, SqlQueryDataVO.class);
		
		Map<String, String> urlParams = ViewUtil.getUrlParams(queryData);
		
		// 构造前端传过来的参数
		sqlQueryDataVO.setParamsData(urlParams);
		
		// 获取自定义sql对象
		FormCustSql viewVo = formCustSqlManager.getByCode(code, true);
		List<FormCustSqlFields> fieldList = viewVo.getFieldsList();
		
		// 获取基本sql和数据源别名
		SqlConstructVO sqlConstructVO = ViewUtil.getSqlConstruct(viewVo, sqlQueryDataVO);
		Map<String, Object> paramsMap = sqlConstructVO.getParamsMap();
		JSONObject freemarkerParams = sqlConstructVO.getFreemarkerParams();
		freemarkerParams.putAll(urlParams);
		
		JSONObject expandJo = JSONObjectUtil.parseExpand(viewVo.getExpand());
		
		SqlViewVO sqlViewVO = ViewUtil.getFieldColumnJo(fieldList, expandJo);
		
		String realSqlColumn = sqlViewVO.getRealSqlColumn();
		String sumSqlColumn = sqlViewVO.getSumSqlColumn();
		
		String sql = sqlConstructVO.getSql();
		
		// 替换系统变量
		sql = ViewUtil.replaceParams(sql);
		
		// 解析freemaker变量
		sql = freemarkerEngine.parseByString(sql, freemarkerParams);
		sql = freemarkerEngine.parseByString(sql, paramsMap); // 非freemaker变量也需要替换一次
		
		// 获取计算总条数的sql
		String queryCountSql = SqlUtil.getCountSql(sql);
		
		// 获取查询分页数据的sql
		String queryDataSql = ViewUtil.getQueryDataSql(realSqlColumn, sql, pageNo, pageSize);
		
		// 获取查询统计数据的sql
		String querySumSql = ViewUtil.getQuerySumSql(sumSqlColumn, realSqlColumn, sql);
		
		sqlViewVO.setFieldList(fieldList);
		sqlViewVO.setQueryCountSql(queryCountSql);
		sqlViewVO.setQueryDataSql(queryDataSql);
		sqlViewVO.setQuerySumSql(querySumSql);
		sqlViewVO.setDsKey(viewVo.getDsKey());
		sqlViewVO.setSqlName(viewVo.getName());
		sqlViewVO.setQueryData(sqlConstructVO.getQueryData());
		sqlViewVO.setParamsMap(paramsMap);
		
		return ResultMsg.SUCCESS(sqlViewVO);
	}

	
	@Override
	public ResultMsg<String> exportData(SqlViewRequest request, HttpServletResponse response) {
		
		String sheetName = "";
		
		String code = request.getCode();
		try {
			
			logger.info("自定义列表-数据导出,code:{}", code);
			
			FormCustSql vo = formCustSqlManager.getByCode(code, true);
			
			List<FormCustSqlFields> settings = ViewUtil.getFields(vo.getFieldsList(), ViewExcuConstants.EXPORT);
			if (CollectionUtils.isEmpty(settings)) {
				logger.error("找不到可以导出的字段列表,code:{}", code);
				return ResultMsg.ERROR("找不到可以导出的字段列表");
			}
			String fieldColumns = request.getFieldColumns();
			if (StringUtils.isNotEmpty(fieldColumns)) {
				String[] fieldColumnsArray = fieldColumns.split(",");
				List<String> fieldSet = Arrays.asList(fieldColumnsArray);
				Iterator<FormCustSqlFields> iterator = settings.iterator();
				while (iterator.hasNext()) {
					FormCustSqlFields tmSqlFieldSettingVO = (FormCustSqlFields) iterator.next();
					if (!fieldSet.contains(tmSqlFieldSettingVO.getFieldName())) {
						iterator.remove();
					}
				}
			}
			
			sheetName = vo.getName();
			
			setResponseContext(response, sheetName);
			
			OutputStream os = response.getOutputStream();
			
			// 创建WritableWorkbook
			WritableWorkbook wb = Workbook.createWorkbook(os);
			
			// 创建WritableSheet
			WritableSheet sheet = wb.createSheet(sheetName, 0);
			
			// 设置列头
			setSheetHeader(sheetName, settings, sheet);
			
			// 设置数据
			setSheetDataList(sheet, settings, request);
			
			wb.write();
			wb.close();
			os.flush();
			os.close();
			logger.info("导出" + sheetName + "列表成功  request=[{}]", request);
			
			// 这里只能返回null,否则会报错,因为wb.close()了, 不能多次返回数据
			return null;
		} catch (Exception e) {
			logger.error("导出" + sheetName + "列表失败 request=[{}] , e : {}", request, e);
			return ResultMsg.ERROR("导出异常:{}" + ExceptionUtil.getRootErrorMseeage(e));
		}
	}
	
	private void setSheetDataList(WritableSheet sheet, List<FormCustSqlFields> settings, SqlViewRequest request)
	        throws Exception {
		
		request.setPageSize(Integer.MAX_VALUE);
		// 获取预览 时的数据
		ResultMsg<SqlViewDataVO> data = listData(request);
		
		SqlViewDataVO sqlViewDataVO = data.getData();
		
		JSONArray list = (JSONArray) sqlViewDataVO.getList();
		
		int col = 0;
		int row = 2;
		int count = 1;
		// 设置每一行的数据
		for (int i = 0; i < list.size(); i++) {
			JSONObject jo = (JSONObject) list.get(i);
			sheet.addCell(new Label(col++, row, "" + count++));
			
			Map<String, JSONObject> dicMap = new HashMap<String, JSONObject>();
			
			for (FormCustSqlFields setting : settings) {
				String fieldName = setting.getFieldName();
				String val = jo.getString(fieldName);
				String format = setting.getDateFormat();
				int controlType = setting.getControlType();
				
				// 获取格式化对象
				JSONObject formatObj = getFormatObj(controlType, format, dicMap);
				
				// 获取数据
				val = getValue(controlType, val, format, formatObj);
				
				sheet.addCell(new Label(col++, row, val));
				
			}
			row++;
			col = 0;
		}
	}
	
	// 获取数据
	private String getValue(int controlType, String val, String format, JSONObject formatObj) {
		// 判断字段类型
		switch (controlType) {
			case (int) FieldsConstants.TEXT_INPUT:// 文本框类型
			// 不需要转
			break;
			case (int) FieldsConstants.SELECT_INPUT:// 下拉框类型
			case (int) FieldsConstants.RADIO:// 下拉框类型
			case (int) FieldsConstants.DIC:// 数据字典
			case (int) FieldsConstants.DIC_RADIO:// [字典]单选框 
				if (formatObj.containsKey(val)) {
					val = formatObj.getString(val);
				}
			break;
			case (int) FieldsConstants.CHECKBOX:// 复选框类型
			case (int) FieldsConstants.DIC_CHECKBOX:// [字典]复选框
				if (StringUtil.isNotEmpty(val)) {
					String[] valArray = val.split(",");
					List<String> valList = new ArrayList<String>();
					for (String tempVal : valArray) {
						if (formatObj.containsKey(tempVal)) {
							val = formatObj.getString(tempVal);
							valList.add(val);
						}
					}
					val = ArrayUtil.join(valList, ",");
				}
			break;
			case (int) FieldsConstants.DATEPICKER:// 日期类型,需要对日期格式化
				Date date = null;
				if (!StringUtils.isEmpty(val) && val.indexOf("{") >= 0) {
					formatObj = JSONObject.parseObject(val);
					date = new Date(formatObj.getLong("time"));
				} else {
					if (StringUtils.isNotEmpty(val)) {
						if (StringUtils.isNumeric(val)) {
							date = new Date(Long.parseLong(val));
						} else {
							date = DateUtils.parseDate(val);
						}
						
					}
				}
				if (BeanUtils.isNotEmpty(date)) {
					if (StringUtils.isEmpty(format)) {
						format = "yyyy-MM-dd HH:mm:ss";
					} else {
						format = format.replaceAll("hh", "HH");
					}
					String dateStr = DateUtils.formatDate(date, format);
					val = dateStr;
				} else {
					val = "";
				}
				
			break;
			case (int) FieldsConstants.JSON:// JSON
				if (StringUtil.isNotEmpty(val)) {
					val = JSONObjectUtil.getValByKey(format, val);
				}
			break;
		}
		return val;
	}
	
	private JSONObject getFormatObj(int controlType, String format, Map<String, JSONObject> dicMap) {
		JSONObject tempJo = new JSONObject();
		
		// 普通下拉框,单选框,复选框
		if (Util.list(FieldsConstants.SELECT_INPUT, FieldsConstants.RADIO, FieldsConstants.CHECKBOX)
		        .contains(controlType)) {
			tempJo = JSONObject.parseObject(format);
			
			return tempJo;
		}
		
		// 数据字典
		if (Util.list(FieldsConstants.DIC, FieldsConstants.DIC_RADIO, FieldsConstants.DIC_CHECKBOX)
		        .contains(controlType)) {
			// 如果是字典类型,则需要获取字典
			if (!dicMap.containsKey(format)) {
				List<DataDictDTO> dictNodeList = dataDictService.getDictNodeList(format, false);
				JSONObject dicJo = new JSONObject();
				dicMap.put(format, dicJo);
				if (!CollectionUtils.isEmpty(dictNodeList)) {
					for (DataDictDTO dic : dictNodeList) {
						dicJo.put(dic.getKey(), dic.getName());
					}
				}
			}
			tempJo = dicMap.get(format);
		}
		
		return tempJo;
	}
	
	private void setSheetHeader(String sheetName, List<FormCustSqlFields> settings, WritableSheet sheet)
	        throws WriteException, RowsExceededException {
		
		sheet.mergeCells(0, 0, settings.size(), 0);
		
		// 格式
		CellFormat titleFormat = JxlUtils.createTitleFormat();
		CellFormat columnFormat = JxlUtils.createColumnFormat();
		sheet.addCell(new Label(0, 0, sheetName, titleFormat));
		int col = 0;
		int row = 1;
		
		// 列名
		sheet.addCell(new Label(col++, row, "序号", columnFormat));
		
		// 设置第一行的列名
		for (FormCustSqlFields setting : settings) {
			sheet.addCell(new Label(col++, row, setting.getFieldDesc(), columnFormat));
		}
	}
	
	private void setResponseContext(HttpServletResponse response, String sheetName)
	        throws UnsupportedEncodingException {
		sheetName = new String(sheetName.getBytes("UTF-8"), "ISO8859-1");
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		response.addHeader("Content-Disposition", "attachment;filename=" + sheetName + ".xls");
	}
	
	/**
	 * 自定义列表-事件按钮方法
	 * @param code
	 * @param buttonAlias
	 * @return
	 * @author xianggang
	 * @date 2019年9月4日 上午9:57:46
	 */
	@Override
	public ResultMsg<String> changeData(String code, String buttonAlias, String ids) {
		
		FormCustSql vo = formCustSqlManager.getByCode(code, true);
		
		String buttons = vo.getButtons();
		if (StringUtil.isEmpty(buttons)) {
			logger.error("自定义按钮不存在,code:{},btnAlias:{}", code, buttonAlias);
			return ResultMsg.ERROR("自定义按钮不存在");
		}
		
		List<SqlButtonVO> buttonList = JSONObjectUtil.toList(buttons, SqlButtonVO.class);
		
		SqlButtonVO btnButtonVO = null;
		for (SqlButtonVO sqlButtonVO : buttonList) {
			if (Util.equals(sqlButtonVO.getAlias(), buttonAlias)
			        && Util.equals(sqlButtonVO.getClickType(), BtnClickTypeConstants.BTNCLICKTYPE_EVENT)) {
				btnButtonVO = sqlButtonVO;
			}
		}
		
		if (btnButtonVO == null) {
			logger.error("自定义按钮不存在,code:{},btnAlias:{}", code, buttonAlias);
			return ResultMsg.ERROR("自定义按钮不存在");
		}
		
		// 获取此次需要修改的字段
		String dataChangeStr = btnButtonVO.getDataChange();
		if (StringUtil.isEmpty(dataChangeStr)) {
			logger.error("自定义按钮数值变化字段为空,code:{},btnAlias:{}", code, buttonAlias);
			return ResultMsg.ERROR("自定义按钮数值变化字段为空");
		}
		
		// 主表
		String tableName = vo.getTableName();
		List<FormCustSqlFields> fieldsList = vo.getFieldsList();
		Map<String, String> fieldNameColumnMap = fieldsList.stream()
		        .filter(fields -> Util.equals(fields.getTableName(), tableName))// 过滤主表的字段
		        .collect(Collectors.toMap(FormCustSqlFields::getFieldName, FormCustSqlFields::getName));
		
		List<String> columnList = fieldsList.stream().map(FormCustSqlFields::getName).collect(Collectors.toList());
		
		String dataChangeSql = "";
		String[] dataChangeArray = dataChangeStr.split(";");
		List<SqlButtonDataChangeVO> dataChangeList = new ArrayList<>();
		for (String dataChange : dataChangeArray) {
			String[] changeData = dataChange.split("=");
			if (changeData.length != 2) {
				logger.error("自定义按钮数值变化配置不正确,code:{},btnAlias:{},dataChange:{}", code, buttonAlias, dataChange);
				return ResultMsg.ERROR("自定义按钮数值变化配置不正确:" + dataChange);
			}
			SqlButtonDataChangeVO dataChangeVO = new SqlButtonDataChangeVO();
			String fieldName = changeData[0];
			String value = changeData[1];
			dataChangeVO.setFieldName(fieldName);
			dataChangeVO.setValue(value);
			// 先判断columnList中是否有该字段,虽然保存自定义sql那里已经做了校验,但是为了防止各种意想不到的情况,这里还是需要再校验一次
			if (columnList.contains(fieldName)) {
				dataChangeVO.setColumn(fieldName);
			} else if (fieldNameColumnMap.containsKey(fieldName)) {
				String column = fieldNameColumnMap.get(dataChangeVO.getFieldName());
				dataChangeVO.setColumn(column);
			} else {
				logger.error("自定义按钮数值变化配置不正确,主表中找不到对应的字段,code:{},btnAlias:{},dataChange:{},column:{}", code,
				        buttonAlias, dataChange, fieldName);
				return ResultMsg.ERROR("自定义按钮数值变化配置不正确,主表中找不到对应的字段:" + dataChange);
			}
			dataChangeSql += dataChangeVO.getColumn() + " = " + dataChangeVO.getValue() + ",";
			dataChangeList.add(dataChangeVO);
		}
		
		ids = ArrayUtil.join(ids.split(","), "','");
		
		dataChangeSql = dataChangeSql.substring(0, dataChangeSql.length() - 1);
		
		// 主键
		String pkName = fieldNameColumnMap.get(vo.getPkName());
		
		String updateSql = "update " + tableName + " set " + dataChangeSql + " where " + pkName + " in (" + ids + ")";
		
		String dsKey = vo.getDsKey();
		
		JdbcTemplate jdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dsKey);
		
		jdbcTemplate.execute(updateSql);
		
		return ResultMsg.SUCCESS("操作成功");
	}
	
}
